package com.hylanda.va.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.hylanda.util.PoiUtil;
import com.hylanda.va.dao.TCostRepository;
import com.hylanda.va.entity.TCost;
import com.hylanda.va.entity.TDepartment;
import com.hylanda.va.model.ApiResponse;
import com.hylanda.va.model.CostQuery;

/**
 * @author zhangy
 * @E-mail:blackoon88@gmail.com
 * @qq:846579287
 * @version created at：2018年5月24日 下午4:29:33 note
 */
@Service
public class TCostService {
	
	private static Logger logger = LoggerFactory.getLogger(TCostService.class);
	@Autowired
	private TCostRepository tCostRepository;
	
	@Autowired
	private TDepartmentService tDepartmentService;
	
	
	@SuppressWarnings("serial")
	public static final Map<String,String> subjectMap =new HashMap<String,String>(){
		{
			put("5502", "管理费用");
			put("5401","主营业务成本");
			put("5402","税金及附加");
			put("5501","销售费用");
			put("5504","研发费用");
			put("5503","管理费用");
		}
	};
	

	/**
	 * 检查文件
	 * @param file
	 * @throws IOException
	 */
	 public   ApiResponse checkFile(MultipartFile file){
	     //判断文件是否存在
	     if(null == file){
	    	 logger.error("文件不存在！");
	         return new ApiResponse("文件不存在","0");
	     }
	     //获得文件名
	     String fileName = file.getOriginalFilename();
	     //判断文件是否是excel文件
	     if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
	    	 logger.error(fileName + "不是excel文件,请上传excel格式!");
	    	 return new ApiResponse(fileName + "不是excel文件,请上传excel格式!","0");
	     }
	     return null;
	 }

	public ApiResponse hanlderExcelData(MultipartFile file,String costMonth) {
		try {
			//获得Workbook工作薄对象
	        Workbook workbook = PoiUtil.getWorkBook(file);
	        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
	        List<String[]> list = new ArrayList<String[]>();
	        if(workbook != null){
	            for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
	                //获得当前sheet工作表
	                Sheet sheet = workbook.getSheetAt(sheetNum);
	                if(sheet == null){
	                    continue; 
	                }
	                //获得当前sheet的开始行
	                int firstRowNum  = sheet.getFirstRowNum();
	                //获得当前sheet的结束行
	                int lastRowNum = sheet.getLastRowNum();
	                //循环除了第一行的所有行
	                for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
	                    //获得当前行
	                    Row row = sheet.getRow(rowNum);
	                    if(row == null){
	                        continue;
	                    }
	                    //获得当前行的开始列
	                    int firstCellNum = row.getFirstCellNum();
	                    //获得当前行的列数
	                    int lastCellNum = row.getLastCellNum();
	                    String[] cells = new String[row.getLastCellNum()];
	                    //循环当前行
	                    for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
	                        Cell cell = row.getCell(cellNum);
	                        cells[cellNum] = PoiUtil.getCellValue(cell);
	                    }
	                    list.add(cells);
	                }
	            }
	        }
	        List<TCost> entitys=new ArrayList<>();
	        Map<String,String> feeMap=new HashMap<>();
	        Map<String,TDepartment> deptMap=new HashMap<>();
	        List<TDepartment> depts=tDepartmentService.getAllDepartment();
	        for(TDepartment dept:depts){
	        	deptMap.put(dept.getDepartmentCode(),dept);
  		  	}
	        //数据解析入库
	        for(String[] datas:list){
	        	if(!datas[1].startsWith("[")){
	        		feeMap.put(datas[0], datas[1]);
	        		if("5503.01".equals(datas[0])||//利息收入
	        				"5503.03".equals(datas[0])){//手续费
	        			String subjectCode=datas[0];
	        			String subjectName=datas[1];
	        			TCost cost=new TCost();
	        			cost.setSubject("5503");
	        			cost.setSubjectCode(subjectCode);//科目代码
			    		cost.setSubjectName(subjectName);//科目名称
			    		cost.setSubjectType(subjectMap.get(cost.getSubject()));//科目类型
		        		cost.setDepartmentName(subjectName);//部门名称
		        		cost.setCostType(feeMap.get(subjectCode));//费用类型
		        		cost.setBorrowedAmount(Double.parseDouble(datas[5]));//本方借方发生额
		        		cost.setCostBelongedDept("集团");//费用归属公司
		        		cost.setPaymentCompany("集团");//付款公司
		        		cost.setCreateTime(new Date());//录入时间
		        		cost.setCostMonth(costMonth);//费用月份
		        		cost.setPrimaryDept("集团公司公共");
       				  	cost.setSecondaryDept("集团公共");
       				  	cost.setUltimateDept("集团公司公共");
	        			entitys.add(cost);
	        		}else if("5402".equals(datas[0])){//主营业务税金及附加
						TCost cost=new TCost();
						String subjectCode=datas[0];
						String subjectName=datas[1];
						cost.setSubject("5402");
						cost.setSubjectCode(subjectCode);//科目代码
						cost.setSubjectName(subjectName);//科目名称
						cost.setSubjectType(subjectMap.get(cost.getSubject()));//科目类型
						cost.setDepartmentName(subjectName);//部门名称
						cost.setCostType(feeMap.get(subjectCode));//费用类型
						cost.setBorrowedAmount(Double.parseDouble(datas[5]));//本方借方发生额
						cost.setCostBelongedDept("集团");//费用归属公司
						cost.setPaymentCompany("集团");//付款公司
						cost.setCreateTime(new Date());//录入时间
						cost.setCostMonth(costMonth);//费用月份
						cost.setPrimaryDept("集团公司公共");
						cost.setSecondaryDept("集团公共");
						cost.setUltimateDept("集团公司公共");
						entitys.add(cost);
	        		}
	        	}else{
	        		  String subjectCode=datas[0];
	        		  if(subjectCode==null) continue;
	        		  String subjectName=datas[1];
	        		  String productLine=null;
	        		  String deptstr=null;
	        		  String departmentCode=null;
	        		  List<String> sc = getSubUtil(subjectName,rgex);
		    		  departmentCode=sc.get(0);
	        		  if(subjectName.indexOf("/")>0){
			    		  String productLineCode=sc.get(1);
			    		  String[] atr=subjectName.split("/");
			    		  deptstr=atr[0].replace("["+departmentCode+"]", "");
			    		  productLine=atr[1].replace("["+productLineCode+"]", "");
	        		  }else{
	        			  deptstr=subjectName.replace("["+departmentCode+"]", "");
	        		  }
		    		 
		    		  
		    		  TCost cost=new TCost();
		    		  cost.setSubjectCode(subjectCode);//科目代码
		    		  cost.setSubjectName(subjectName);//科目名称
		    		  cost.setSubject(subjectCode.indexOf(".")>0?subjectCode.substring(0, subjectCode.indexOf(".")):subjectCode);//科目
		    		  cost.setSubjectType(subjectMap.get(cost.getSubject()));//科目类型
	        		  cost.setProductLine(productLine);//产品线
	        		  cost.setDepartmentName(deptstr);//部门名称
	        		  cost.setCostType(feeMap.get(subjectCode));//费用类型
	        		  cost.setBorrowedAmount(datas[5]==null?0:Double.parseDouble(datas[5]));//本方借方发生额
	        		  cost.setCostBelongedDept(deptMap.get(departmentCode)==null?null:deptMap.get(departmentCode).getCostBelongedDept());//费用归属公司
	        		  cost.setPaymentCompany(deptMap.get(departmentCode)==null?null:deptMap.get(departmentCode).getPaymentCompany());//付款公司
	        		  cost.setCreateTime(new Date());//录入时间
	        		  cost.setCostMonth(costMonth);//费用月份
	        		  //部门
	        		  String fullDeptName=deptMap.get(departmentCode)==null?null:deptMap.get(departmentCode).getDepartmentFullName();
	        		  if(fullDeptName!=null){
	        			  String[] depp=fullDeptName.split("_");
	        			  if(depp.length==1){
	        				  cost.setPrimaryDept(depp[0]);
	        				  cost.setSecondaryDept(depp[0]);
	        				  cost.setUltimateDept(depp[0]);
	        			  }else if(depp.length==2){
	        				  cost.setPrimaryDept(depp[0]);
	        				  cost.setSecondaryDept(depp[1]);
	        				  cost.setUltimateDept(depp[1]);
	        			  }else if(depp.length>=3){
	        				  cost.setPrimaryDept(depp[0]);
	        				  cost.setSecondaryDept(depp[1]);
	        				  cost.setUltimateDept(depp[depp.length-2]);
	        			  }
	        		  }
	        		  entitys.add(cost);
	        	}
	        }
	        
	        //数据入库
	        tCostRepository.save(entitys);
		} catch (Exception e) {
			return new ApiResponse("上传处理失败！","0");
		}
		
		return null;
	}
	static final String rgex = "\\[(.*?)\\]";
	public static void main(String[] args) {
//		String str="[999.03]行政公共-北京/[0000]公共";
//		
//        System.out.println(getSubUtil(str,rgex));   
//        List<String> lists = getSubUtil(str,rgex);  
//        for (String string : lists) {  
//            System.out.println(string);  
//        }  
		String depts="集团运营管理";
		depts.split("_");
		
		System.out.println("123.2.2".substring(0, "123.2.2".indexOf(".")));
//        System.out.println(getSubUtilSimple(str, rgex));  
	}
	
	/**  
     * 正则表达式匹配两个指定字符串中间的内容  
     * @param soap  
     * @return  
     */    
    public static List<String> getSubUtil(String soap,String rgex){    
        List<String> list = new ArrayList<String>();    
        Pattern pattern = Pattern.compile(rgex);// 匹配的模式    
        Matcher m = pattern.matcher(soap);    
        while (m.find()) {    
            int i = 1;    
            list.add(m.group(i));    
            i++;    
        }    
        return list;    
    }    
        
    /**  
     * 返回单个字符串，若匹配到多个的话就返回第一个，方法与getSubUtil一样  
     * @param soap  
     * @param rgex  
     * @return  
     */    
    public static String getSubUtilSimple(String soap,String rgex){    
        Pattern pattern = Pattern.compile(rgex);// 匹配的模式    
        Matcher m = pattern.matcher(soap);    
        while(m.find()){    
            return m.group(1);
        }    
        return "";    
    }

	public void checkData(String cost_month) {
		List<TCost> l=tCostRepository.findByCostMonth(cost_month);
		if(l!=null&&l.size()>0){
			tCostRepository.deleteByCostMonth(cost_month);
		}
	}

	public Page<TCost> findCostCriteria(Integer page, Integer size,
			final CostQuery costQuery) {
		Pageable pageable = new PageRequest(page, size);  
        Page<TCost> orderPage = tCostRepository.findAll(new Specification<TCost>(){  
            @Override  
            public Predicate toPredicate(Root<TCost> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {  
                List<Predicate> list = new ArrayList<Predicate>();  
                if(null!=costQuery.getSubject()&&!"".equals(costQuery.getSubject())){  
                    list.add(criteriaBuilder.equal(root.get("subject").as(String.class), costQuery.getSubject()));  
                }  
                if(null!=costQuery.getSubjectType()&&!"".equals(costQuery.getSubjectType())){  
                    list.add(criteriaBuilder.equal(root.get("subjectType").as(String.class), costQuery.getSubjectType()));  
                }
                if(null!=costQuery.getPaymentCompany()&&!"".equals(costQuery.getPaymentCompany())){  
                    list.add(criteriaBuilder.equal(root.get("paymentCompany").as(String.class), costQuery.getPaymentCompany()));  
                }
                if(null!=costQuery.getSubjectCode()&&!"".equals(costQuery.getSubjectCode())){  
                    list.add(criteriaBuilder.equal(root.get("subjectCode").as(String.class), costQuery.getSubjectCode()));  
                }
                if(null!=costQuery.getSubjectName()&&!"".equals(costQuery.getSubjectName())){  
                    list.add(criteriaBuilder.equal(root.get("subjectName").as(String.class), costQuery.getSubjectName()));  
                }
                if(null!=costQuery.getDepartmentName()&&!"".equals(costQuery.getDepartmentName())){  
                    list.add(criteriaBuilder.equal(root.get("departmentName").as(String.class), costQuery.getDepartmentName()));  
                }
                if(null!=costQuery.getProductLine()&&!"".equals(costQuery.getProductLine())){  
                    list.add(criteriaBuilder.equal(root.get("productLine").as(String.class), costQuery.getProductLine()));  
                }
                
                if(null!=costQuery.getCostMonth()&&!"".equals(costQuery.getCostMonth())){  
                    list.add(criteriaBuilder.equal(root.get("costMonth").as(String.class), costQuery.getCostMonth()));  
                }
                if(null!=costQuery.getCostType()&&!"".equals(costQuery.getCostType())){  
                    list.add(criteriaBuilder.equal(root.get("costType").as(String.class), costQuery.getCostType()));  
                }
                if(null!=costQuery.getUltimateDept()&&!"".equals(costQuery.getUltimateDept())){  
                    list.add(criteriaBuilder.equal(root.get("ultimateDept").as(String.class), costQuery.getProductLine()));  
                }
                if(null!=costQuery.getSecondaryDept()&&!"".equals(costQuery.getSecondaryDept())){  
                    list.add(criteriaBuilder.equal(root.get("secondaryDept").as(String.class), costQuery.getSecondaryDept()));  
                }
                if(null!=costQuery.getPrimaryDept()&&!"".equals(costQuery.getPrimaryDept())){  
                    list.add(criteriaBuilder.equal(root.get("primaryDept").as(String.class), costQuery.getPrimaryDept()));  
                }
                if(null!=costQuery.getCostBelongedDept()&&!"".equals(costQuery.getCostBelongedDept())){  
                    list.add(criteriaBuilder.equal(root.get("costBelongedDept").as(String.class), costQuery.getCostBelongedDept()));  
                }
                Predicate[] p = new Predicate[list.size()];
                return criteriaBuilder.and(list.toArray(p));  
            }  
        },pageable);  
        return orderPage;  
	}
	
	public final static String[] TITLE = {"ID","科目","科目类别","付款公司名","科目代码","科目名称","部门名称","产品线","费用年月",
		"本期借方发生额","费用类型","终极部门","二级部门","一级部门","费用归属部门"};
	public String genExcel(CostQuery costQuery) {
		String fileName = UUID.randomUUID().toString()+System.currentTimeMillis() + ".xlsx";
		File file = new File("tmp/" + fileName);
		if (file.exists()) {
			file.delete();
		}
		Page<TCost> page=findCostCriteria(0, 10000, costQuery);
		List<TCost> list =page.getContent();
		SXSSFWorkbook wb = new SXSSFWorkbook(1000);
		FileOutputStream fileOut = null;
		try {
			Sheet sheet = wb.createSheet("费用明细");
//			CellStyle style = wb.createCellStyle();  
//		    style.setAlignment(HorizontalAlignment.CENTER);  
//		    style.setVerticalAlignment(VerticalAlignment.CENTER); 
		    sheet.setVerticallyCenter(true);
		    sheet.setHorizontallyCenter(true);
			Row row_title = sheet.createRow(0);
			for (int i = 0; i < TITLE.length; i++) {
				Cell cell = row_title.createCell(i, CellType.STRING);
				cell.setCellValue(TITLE[i]);
				if(i==0){
					sheet.setColumnWidth(i, TITLE[i].getBytes().length*256*3);
				}else{
					sheet.setColumnWidth(i, TITLE[i].getBytes().length*256*2);
				}
				
			}
			int idx = 1;
			for (TCost cost : list) {
				Row row = sheet.createRow(idx++);
				Cell cell_0 = row.createCell(0, CellType.NUMERIC);
				cell_0.setCellValue(cost.getId());
				Cell cell_1 = row.createCell(1, CellType.STRING);
				cell_1.setCellValue(cost.getSubject());
				Cell cell_2 = row.createCell(2, CellType.STRING);
				cell_2.setCellValue(cost.getSubjectType());
				Cell cell_3 = row.createCell(3, CellType.STRING);
				cell_3.setCellValue(cost.getPaymentCompany());
				Cell cell_4 = row.createCell(4, CellType.STRING);
				cell_4.setCellValue(cost.getSubjectCode());
				Cell cell_5 = row.createCell(5, CellType.STRING);
				cell_5.setCellValue(cost.getSubjectName());
				Cell cell_6 = row.createCell(6, CellType.STRING);
				cell_6.setCellValue(cost.getDepartmentName());
				Cell cell_7 = row.createCell(7, CellType.STRING);
				cell_7.setCellValue(cost.getProductLine());
				Cell cell_8 = row.createCell(8, CellType.STRING);
				cell_8.setCellValue(cost.getCostMonth());
				Cell cell_9 = row.createCell(9, CellType.NUMERIC);
				cell_9.setCellValue(cost.getBorrowedAmount());
				Cell cell_10 = row.createCell(10, CellType.STRING);
				cell_10.setCellValue(cost.getCostType());
				Cell cell_11 = row.createCell(11, CellType.STRING);
				cell_11.setCellValue(cost.getUltimateDept());
				Cell cell_12 = row.createCell(12, CellType.STRING);
				cell_12.setCellValue(cost.getCostBelongedDept());
				Cell cell_13 = row.createCell(13, CellType.STRING);
				cell_13.setCellValue(cost.getSecondaryDept());
				Cell cell_14 = row.createCell(14, CellType.STRING);
				cell_14.setCellValue(cost.getPrimaryDept());
			}
			list=null;
			fileOut = new FileOutputStream(file);
			wb.write(fileOut);
			// dispose of temporary files backing this workbook on disk
			wb.dispose();
		} catch (Exception e) {
			logger.error("导出excel错误", e);
			e.printStackTrace();
		} finally {
			try {
				fileOut.close();
				wb.close();
			} catch (IOException e) {
			}
		}
		return fileName;
	}   
}
